package lib_model.Account;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import lib_dal.Connection.ConnectMSSQL;
import lib_dal.Interface.ISql;
import lib_model.Orther.ReturnObj;

public class Account_warehouse implements ISql {

	public String username_ref;
	public int warehouse_id_ref;
	public String role;
	public String date_create;

	public Account_warehouse() {
	}

	public Account_warehouse(String username_ref) {
		super();
		this.username_ref = username_ref;
	}

	@Override
	public ReturnObj Select() {
		String sql = "select username_ref, warehouse_id_ref, role, date_create from dbo.account_warehouse";

		ReturnObj obj = new ReturnObj();
		ArrayList<Account_warehouse> arr = new ArrayList<Account_warehouse>();
		try {
			PreparedStatement preState = new ConnectMSSQL()
					.preparedStatement(sql);
			ResultSet rs = preState.executeQuery();
			while (rs.next()) {
				Account_warehouse a = new Account_warehouse();
				a.username_ref = rs.getString("username_ref");
				a.warehouse_id_ref = rs.getInt("warehouse_id_ref");
				a.role = rs.getString("role");
				arr.add(a);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		obj.ObjResult = arr;
		return obj;
	}

	@Override
	public ReturnObj Add() {
		String sql = "insert into dbo.account_warehouse (username_ref,   warehouse_id_ref,   role,date_create) values   (?,   ?,   ?,GETDATE())";
		ReturnObj obj = new ReturnObj();
		try {
			PreparedStatement preState = new ConnectMSSQL()
					.preparedStatement(sql);
			preState.setString(1, this.username_ref);
			preState.setInt(2, this.warehouse_id_ref);
			preState.setString(3, this.role);
			obj.Result = (preState.executeUpdate() != 0) ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return obj;
	}

	@Override
	public ReturnObj Update() {
		String sql = "update dbo.account_warehouse set warehouse_id_ref = ?,   role = ? where  username_ref = ?";
		ReturnObj obj = new ReturnObj();
		try {
			PreparedStatement preState = new ConnectMSSQL()
					.preparedStatement(sql);
			preState.setInt(1, this.warehouse_id_ref);
			preState.setString(2, this.role);
			preState.setString(3, this.username_ref);
			obj.Result = (preState.executeUpdate() != 0) ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return obj;
	}

	@Override
	public ReturnObj Delete() {
		String sql = "delete from dbo.account_warehouse where username_ref = ?";
		ReturnObj obj = new ReturnObj();
		try {
			PreparedStatement preState = new ConnectMSSQL()
					.preparedStatement(sql);
			preState.setString(1, this.username_ref);
			obj.Result = preState.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return obj;
	}

	@Override
	public ReturnObj GetInformation() {
		ReturnObj obj = new ReturnObj();
		String sql = "select username_ref, warehouse_id_ref, role from dbo.account_warehouse where username_ref = ?";
		try {

			PreparedStatement preState = new ConnectMSSQL()
					.preparedStatement(sql);
			preState.setString(1, this.username_ref);
			ResultSet rs = preState.executeQuery();
			while (rs.next()) {
				Account_warehouse a = new Account_warehouse();
				a.username_ref = rs.getString("username_ref");
				a.warehouse_id_ref = rs.getInt("warehouse_id_ref");
				a.role = rs.getString("role");
				obj.ObjResult = a;
			}

		} catch (SQLException e) {

			e.printStackTrace();
		}
		return obj;
	}

	public ReturnObj SelectByIDwarehouse(int id) {
		String sql = "select username_ref, warehouse_id_ref, role, date_create from dbo.account_warehouse where warehouse_id_ref = "
				+ id;

		ReturnObj obj = new ReturnObj();
		ArrayList<Account_warehouse> arr = new ArrayList<Account_warehouse>();
		try {
			PreparedStatement preState = new ConnectMSSQL()
					.preparedStatement(sql);
			ResultSet rs = preState.executeQuery();
			while (rs.next()) {
				Account_warehouse a = new Account_warehouse();
				a.username_ref = rs.getString("username_ref");
				a.warehouse_id_ref = rs.getInt("warehouse_id_ref");
				a.role = rs.getString("role");
				a.date_create =rs.getDate("date_create").toString();
				arr.add(a);
			}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		obj.ObjResult = arr;
		return obj;
	}
}
